package sample;

import cn.hutool.http.HttpUtil;
import cn.hutool.json.JSONUtil;
import javafx.scene.Scene;

import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.sql.*;

public class oilDao<puibic> {
    private static String dbUser = "root";
    private static String dbPassword = "7878478";
    private static String coonUrl = "jdbc:mysql://localhost:3306/shiyou?characterEncoding=utf-8&useUnicode=true&serverTimezone=UTC";
    private  static Connection conn;
public oilDao(){
    conn = DbUtil.getMySQLConnection(coonUrl,dbUser,dbPassword);
}


    public static oilMessage getSqlConnection(String city) throws SQLException, UnsupportedEncodingException {

        Statement statement = conn.createStatement();
        PreparedStatement ps=null;
        oilMessage oil = new oilMessage();
        oil.setProvince(city);
        if(isExist(city)){
            String sql = "select * from information where province ="+"\""+city+"\"";
            ResultSet rs = statement.executeQuery(sql);
            while(rs.next()){
                    oil.setProvince(rs.getString("province"));
                    oil.setP0(rs.getString(2));
                    oil.setP89(rs.getString(3));
                    oil.setP92(rs.getString(4));
                    oil.setP95(rs.getString(5));
                    oil.setUpdate_time(rs.getString(6));


            }
            rs.close();
            statement.close();
            return oil;

        }else {
            String urlStr = URLEncoder.encode(city,"UTF-8");
            String  geturl = "https://qqlykm.cn/api/oilprice/get?key=6i9Khjgl7RlqRQHHU0tmhGknvg&name="+urlStr;
            String result1 = HttpUtil.get(geturl);
            String result2 ="{"+result1.substring(16,32);
            String result3 = result1.replace("{\"success\":true,\"province\":\"江西\",\"data\":{",result2);
            String result4 = result1.substring(91,result1.length());
            String resultend = result3.replace("},\"update_time\":\"2022-11-07 12:05\"}",result4);
            oil =  (oilMessage) JSONUtil.toBean(resultend, oilMessage.class);

            String sql2 = "insert into information(province,p0,p89,p92,p95,update_time) values(?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql2);
            ps.setString(1,oil.getProvince());
            ps.setString(2,oil.getP0());
            ps.setString(3,oil.getP89());
            ps.setString(4,oil.getP92());
            ps.setString(5,oil.getP95());
            ps.setString(6,oil.getUpdate_time());
            ps.executeUpdate();
            System.out.println("测试是否运行");

            ps.close();
//
            return oil;
        }

    }
    public static boolean isExist(String city) throws SQLException {
    Statement statement = conn.createStatement();
 //   String sql = "SELECT name,updateTime from information where `name`=\""+city+"\" "+"and"+" locate("+"\""+data+"\""+",updateTime)";
//    System.out.println(sql);
        String sql = "SELECT province from information where `province`=\""+city+"\"";
    ResultSet rs = statement.executeQuery(sql);
    oilMessage oil2 = new oilMessage();
    while(rs.next()){
        oil2.setProvince(rs.getString("province"));
    }
    if(oil2.getProvince()==null){

        rs.close();
        statement.close();
        return false;
    } else {
        rs.close();
        statement.close();
        return true;
    }
    }
    public static boolean isExistCity(String city) throws UnsupportedEncodingException {
        String urlStr = URLEncoder.encode(city,"UTF-8");
        String  geturl = "https://qqlykm.cn/api/oilprice/get?key=6i9Khjgl7RlqRQHHU0tmhGknvg&name="+urlStr;
        String result1 = HttpUtil.get(geturl);
        System.out.println(result1);
        if(result1.contains("false")){
            return true;
        }
        else {
            return false;
        }
    }

    public static boolean isNew(String data) throws SQLException {
        String sql = "SELECT updateTime from information where " +"locate("+"\""+data+"\""+",updateTime)";
        Statement statement = conn.createStatement();
        ResultSet rs = statement.executeQuery(sql);
        Message message_2 = new Message();
        while(rs.next()){
            message_2.setUpdateTime(rs.getString("updateTime"));
        }
        if(message_2.getUpdateTime()!=null){

            rs.close();
            statement.close();
            return true;
        }
        else {
            rs.close();
            statement.close();
            return false;
        }

    }


}
